package com.zcily.utils.excel;

import org.apache.poi.ss.usermodel.*;

import java.math.BigDecimal;
import java.text.Format;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;

public class ExcelUtil {

    /**
     * 把excel列的英文名称转为索引（索引值从1开始）；例如：第AB列的列数为28
     */
    public static int getExcelColumnNumber(String str) {
        if (!str.matches("^[A-Za-z]+$")) {
            throw new RuntimeException("excel列的英文名称错误：名称只能为英文字母");
        }
        int num = 0;
        char[] array = str.toUpperCase().toCharArray();
        int length = array.length;
        for (int i = 0; i < length; i++) {
            num += (array[i] - 'A' + 1) * BigDecimal.valueOf(26).pow(length - i - 1).intValue();
        }
        return num;
    }

    /**
     * 把excel列的索引（索引值从1开始）转为英文名；例如：第28列对于的列名为AB
     */
    public static String getExcelColumnString(int num) {
        if (num < 1) {
            throw new RuntimeException("excel列的索引值错误：索引值不得小于1");
        }
        String str = "";
        while (true) {
            if (num == 0) {
                break;
            }
            str = (char) ('A' + num % 26 - 1) + str;
            num = num / 26;
        }
        return str;
    }

    /**
     * 把excel中的日期转换为java中的Date对象。<p/>
     * 例如：45733.6399319792 转换后的时间是：2025-03-17 15:21:30.123
     *
     * @param ditNumber excel中的日期，例如：45733.6399319792
     * @return
     */
    public static Date getTime(String ditNumber) {
        double num = Double.parseDouble(ditNumber);
        Calendar calendar = Calendar.getInstance();
        // 25569 是 从 1900年1月1日到 1970年 1月1日的相隔天数
        // 86400 是一天（24小时）的秒数
        calendar.setTimeInMillis((long) (((num - 25569) * 86400 * 1000 - TimeZone.getDefault().getRawOffset())));
        return calendar.getTime();
    }

    public void setCellValue(Cell cell, Object value) {
        setCellValue(cell, value, null);
    }

    public void setCellValue(Cell cell, Object value, Format format) {
        if (cell == null || value == null) {
            return;
        }
        if (format != null) {
            cell.setCellValue(format.format(value));
            return;
        }
        if (value instanceof String) {
            cell.setCellValue((String) value);
            return;
        }
        if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
            return;
        }
        if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
            return;
        }
        if (value instanceof Date) {
            cell.setCellValue((Date) value);
            return;
        }
        if (value instanceof Calendar) {
            cell.setCellValue((Calendar) value);
            return;
        }
        if (value instanceof LocalDate) {
            cell.setCellValue((LocalDate) value);
            return;
        }
        if (value instanceof LocalDateTime) {
            cell.setCellValue((LocalDateTime) value);
            return;
        }
        if (value instanceof RichTextString) {
            cell.setCellValue((RichTextString) value);
            return;
        }
        cell.setCellValue(value.toString());
    }

    public Object getCellValue(Cell cell) {
        return getCellValue(cell, (FormulaEvaluator) null);
    }

    public Object getCellValue(Cell cell, FormulaEvaluator evaluator) {
        if (cell == null) {
            return null;
        }
        Object value = null;
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case FORMULA:
                if (evaluator == null) {
                    value = cell.getCellFormula();
                } else {
                    CellValue cellValue = evaluator.evaluate(cell);
                    switch (cellValue.getCellType()) {
                        case STRING:
                            value = cellValue.getStringValue();
                            break;
                        case NUMERIC:
                            value = cellValue.getNumberValue();
                            break;
                        case BOOLEAN:
                            value = cellValue.getBooleanValue();
                            break;
                        case ERROR:
                            value = cellValue.getErrorValue();
                    }
                }
                break;
            case BLANK:
                break;
            case ERROR:
                value = cell.getErrorCellValue();
                break;
        }
        return value;
    }
}
